package com.ashen.boot.admin.project.system.gs.service.impl;

import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.OrderItem;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ashen.boot.admin.commons.utils.QueryWrapperUtils;
import com.ashen.boot.admin.domain.service.impl.SuperBaseServiceImpl;
import com.ashen.boot.admin.project.system.gs.mapper.IInfoGsMapper;
import com.ashen.boot.admin.project.system.gs.model.dto.InfoGsDTO;
import com.ashen.boot.admin.project.system.gs.model.dto.InfoGsExcelDTO;
import com.ashen.boot.admin.project.system.gs.model.entity.InfoGsEntity;
import com.ashen.boot.admin.project.system.gs.model.query.InfoGsParams;
import com.ashen.boot.admin.project.system.gs.service.IInfoGsService;
import com.hb0730.commons.lang.StringUtils;
import com.hb0730.commons.lang.collection.CollectionUtils;
import com.hb0730.commons.spring.BeanUtils;
import lombok.RequiredArgsConstructor;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Nonnull;
import java.io.*;
import java.util.*;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author gao
 * @since 2021-10-28
 */
@Service
@RequiredArgsConstructor
public class InfoGsServiceImpl extends SuperBaseServiceImpl<Long, InfoGsParams, InfoGsDTO, InfoGsEntity, IInfoGsMapper> implements IInfoGsService {

//    @Override
//    public List<InfoGsDTO> list(@Nonnull InfoGsParams params) {
//        List<InfoGsDTO> list = super.list(params);
//        if (CollectionUtils.isEmpty(list)) {
//            return list;
//        }
//        return list;
//    }


    @Override
    public Page<InfoGsDTO> page(@Nonnull InfoGsParams params) {
        System.out.println(JSON.toJSONString(params));
        QueryWrapper<InfoGsEntity> query = this.query(params);
        if (StringUtils.isNotBlank(params.getGsdm())) {
            query.like(InfoGsEntity.GSDM, params.getGsdm());
        }
        if (StringUtils.isNotBlank(params.getGsmc())) {
            query.like(InfoGsEntity.GSMC, params.getGsmc());
        }
        if (Objects.nonNull(params.getIsEnabled())) {
            query.eq(InfoGsEntity.IS_ENABLED, params.getIsEnabled());
        }
        Page<InfoGsEntity> page = QueryWrapperUtils.getPage(params);

        //这是添加排序，sqlserver分页需要
        OrderItem orderItem = OrderItem.desc("gsdm");
        page.addOrder(orderItem);
        System.out.println(JSON.toJSONString(query));
        page = super.page(page, query);
        Page<InfoGsDTO> pageInfo = QueryWrapperUtils.pageToBean(page, InfoGsDTO.class);
        List<InfoGsDTO> records = pageInfo.getRecords();
        if (!CollectionUtils.isEmpty(records)) {

        }
        return pageInfo;
    }

    @Override
    public String batchImport(MultipartFile mfile) {
//        File uploadDir = new File("F:\\Users\\xzxy\\excel\\upload\\");
        File uploadDir = new File("/Users/longsheng/Desktop/");
        // 创建一个目录 （它的路径名由当前 File 对象指定，包括任一必须的父路径。）
        if (!uploadDir.exists())
            uploadDir.mkdirs();
        // 新建一个文件
        File tempFile = new File("/Users/longsheng/Desktop/" + new Date().getTime() + ".xlsx");
        // 初始化输入流
        InputStream is = null;
        try {
            // 将上传的文件写入新建的文件中
            mfile.transferTo(tempFile);

            // 根据新建的文件实例化输入流
            is = new FileInputStream(tempFile);

            // 根据版本选择创建Workbook的方式
            Workbook wb = null;

            // 判断文件是2003版本还是2007版本
            if (!is.markSupported()) {
                is = new PushbackInputStream(is, 8);
            }
//            if (POIXMLDocument.hasOOXMLHeader(is)) {
//                System.out.println("2007及以上");
//                wb = new XSSFWorkbook(is);
//            } else {
//                System.out.println("2003及以下");
//                wb = new HSSFWorkbook(is);
//            }
            wb = WorkbookFactory.create(is);
            // 根据excel里面的内容读取知识库信息
            return readExcelValue(wb, tempFile);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    is = null;
                    e.printStackTrace();
                }
            }
        }
        return "导入出错！请检查数据格式！";
    }


    private String readExcelValue(Workbook wb, File tempFile) {

        // 错误信息接收器
        String errorMsg = "";
        List<InfoGsEntity> StudentHist = new ArrayList<InfoGsEntity>();
        // 历史学生实体类
        InfoGsEntity sh;

        for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) {
            Sheet sheet = wb.getSheetAt(numSheet);

            // 得到Excel的行数
            int totalRows = sheet.getPhysicalNumberOfRows();
            // 总列数
            int totalCells = 0;
            // 得到Excel的列数(前提是有行数)，从第二行算起
            if (totalRows >= 2 && sheet.getRow(1) != null) {
                totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
            }

            String br = "<br/>";

            System.out.println("总行数:" + totalRows + "总列数:" + totalCells);

            // 循环Excel行数,从第二行开始。标题不入库
            for (int r = 1; r < totalRows; r++) {
                String rowMessage = "";
                Row row = sheet.getRow(r);
                if (row == null || 0 == row.getPhysicalNumberOfCells()) {
                    errorMsg += br + "第" + (r + 1) + "行数据为空，请仔细检查！";
                    continue;
                }
                sh = new InfoGsEntity();


                String gsdm;
                String gsmc;
                String zt;
                Integer xh;

                // 循环Excel的列
                for (int c = 0; c < totalCells; c++) {
                    Cell cell = row.getCell(c);
                    if (null != cell) {
                        if (c == 0) {
                            gsdm = cell.getStringCellValue();
                            if (StringUtils.isEmpty(gsdm)) {
                                rowMessage += "第" + (c + 1) + "列不能为空；";
                            } else if (gsdm.length() > 20) {
                                rowMessage += "第" + (c + 1) + "列字数不能超过20；";
                            }
                            sh.setGsdm(gsdm);
                        } else if (c == 1) {
                            gsmc = cell.getStringCellValue();
                            if (StringUtils.isEmpty(gsmc)) {
                                rowMessage += "第" + (c + 1) + "列不能为空；";
                            } else if (gsmc.length() > 50) {
                                rowMessage += "第" + (c + 1) + "列字数不能超过50；";
                            }
                            sh.setGsmc(gsmc);
                        } else if (c == 2) {
                            zt = cell.getStringCellValue();
                            if (StringUtils.isEmpty(zt)) {
                                rowMessage += "第" + (c + 1) + "列不能为空；";
                            }
                            sh.setZt(zt);
                        } else if (c == 3) {
                             xh = Math.toIntExact(Math.round(cell.getNumericCellValue()));
                            if (0==(xh)) {
                                rowMessage += "第" + (c + 1) + "列不能为空且不能为0；";
                            }
                            sh.setXh(xh);
                        }
                    } else {
                        rowMessage += "第" + (c + 1) + "列不能为空；";
                    }
                }
                // 拼接每行的错误提示
                if (!StringUtils.isEmpty(rowMessage)) {
                    errorMsg += br + "第" + (r + 1) + "行，" + rowMessage;
                } else {
                    StudentHist.add(sh);
                }
            }
        }

        // 删除上传的临时文件
        if (tempFile.exists())

        {
            tempFile.delete();
        }

        // 全部验证通过才导入到数据库
        System.out.println("errorMsg"+errorMsg);
        if (StringUtils.isEmpty(errorMsg)) {
            // 将没有问题的数据导入数据库
            for (InfoGsEntity studentHist : StudentHist) {
                super.save(studentHist);
            }
         }
        //errorMsg = "本次共成功导入" + StudentHist.size() + "条数据！其中" + errorMsg;
        return errorMsg;
    }


    @Override
    @Nonnull
    public QueryWrapper<InfoGsEntity> query(@Nonnull InfoGsParams params) {
        QueryWrapper<InfoGsEntity> query = QueryWrapperUtils.getQuery(params);
        if (StringUtils.isNotBlank(params.getGsdm())) {
            query.like(InfoGsEntity.GSDM, params.getGsdm());
        }
        if (StringUtils.isNotBlank(params.getGsmc())) {
            query.eq(InfoGsEntity.GSMC, params.getGsmc());
        }
        if (Objects.nonNull(params.getIsEnabled())) {
            query.eq(InfoGsEntity.IS_ENABLED, params.getIsEnabled());
        }
        return query;
    }
    

    @Override
    public List<InfoGsExcelDTO> export(@Nonnull InfoGsParams params) {
        QueryWrapper<InfoGsEntity> query = this.query(params);
        List<InfoGsEntity> entities = super.list(query);
        return BeanUtils.transformFromInBatch(entities, InfoGsExcelDTO.class);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean upload(Collection<InfoGsExcelDTO> dataList) {
        if (com.baomidou.mybatisplus.core.toolkit.CollectionUtils.isEmpty(dataList)) {
            return false;
        }
        List<InfoGsEntity> entities = BeanUtils.transformFromInBatch(dataList, InfoGsEntity.class);
        return this.saveBatch(entities);
    }
}
